package com.foreknow.demo10;

import javax.swing.*;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class DBUtil {
        private Connection conn;
        private ResultSet rs;
        private PreparedStatement preparedStatement;
    /**
     * 连接数据库
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public void getConnection() throws ClassNotFoundException, SQLException {
        //1. 加载驱动程序
        Class.forName("com.mysql.jdbc.Driver");
        //2. 获取连接(Connection) 注意：别忘了改数据库名和密码，否则无法连接
        conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/basic14","root","123456");
    }

    /**
     * 查询
     * @param sql
     * @return
     * @throws SQLException
     * 问题：要将Statement改成 PreparedStatement
     * 分析： 调用方法QueryInfo("select * from admin where name = ? and pass=? ")
     */
    public ResultSet QueryInfo(String sql,Object...values) throws SQLException {
        preparedStatement = conn.prepareStatement(sql);
        //需要遍历values数组
        for(int i = 0;i<values.length;i++){
            preparedStatement.setObject(i+1,values[i]);
        }
        rs = preparedStatement.executeQuery();
        return rs;
    }

    /**
     * insert delete update
     * @param sql
     * @return
     * @throws SQLException
     * 问题：要将Statement改成 PreparedStatement
     */
    public int DML(String sql,Object...values) throws SQLException {
        preparedStatement = conn.prepareStatement(sql);
        //需要遍历values数组
        for(int i = 0;i<values.length;i++){
            preparedStatement.setObject(i+1,values[i]);
        }
        int isRight = preparedStatement.executeUpdate();
        return isRight;
    }

    /**
     * 关闭资源
     * @throws SQLException
     */
    public void closed() throws SQLException {
        if(rs != null) {
            rs.close();
        }
        if(preparedStatement != null) {
            preparedStatement.close();
        }
        if(conn != null) {
            conn.close();
        }
    }

    public static void main(String[] args) {
        DBUtil dbUtil = new DBUtil();
        try {
            //1. 调用方法连接数据库
            dbUtil.getConnection();
            //2. 查询数据
//            ResultSet rs = dbUtil.QueryInfo("select * from admin where name = ? and pass = ?","fender","123456");
//            //遍历结果集获取到结果集中的数据
//            List<Admin> list = new ArrayList<>();
//            while (rs.next()) {
//                //获取到当前行的每一列数据
//                int id = rs.getInt("ID");
//                String name = rs.getString("NAME");
//                String pass = rs.getString("PASS");
//
//                //将获取到的数据保存到对象中
//                Admin admin = new Admin();
//                admin.setId(id);
//                admin.setName(name);
//                admin.setPass(pass);
//
//                //将对象保存到List集合中
//                list.add(admin);
//            }
//            for (Admin admin:list) {
//                System.out.println(admin.getName());
//            }

            //测试DML方法
            int i = dbUtil.DML("update admin set name = ?,pass = ? where id = ?","def","333333",5);
            System.out.println(i);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
